Triky query to join tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Triky query to join tables

Author

Topic

marekpracz
Starting Member

7 Posts

Posted - 2010-11-18 : 13:37:19

HI ,

Trick is that I've got 4 tables

Sale , sale_details, sales_message, message_text

Table:Sales
Sales_id

Table: Sales_product
Sales_id, sales_product_id

Table: Sales_message
Sales_id, sales_message_id, message_text_id

Table:Message_text,
Message_text_id, message_text

Thing is how can I join those tables to get all the sales ID's that have at least two products one of which has got any number of messages and the other one hasn't got ANY any messages.
So another words one sale entity that has both products with messages and at lease one with no message.
Something like this

Sales 1 ID 123
Product1 ID 1234 for sale ID 123
Product2 ID 4321 for sale ID 123
Sales_message ID 098 for product ID 1234 'blabla'
Sales_message ID 876 for product ID 1234 'albalb'
AND NO MESSAGES FOR PRODUCT 4321
This is what I want to get from DB.
I'm seating here for quite some time and I can't figure it out .

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-18 : 16:56:32

Do you have some decent sample data you can supply in a consumable format? This link can help with that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

The query is, or shouldn't be too tough. However, I'm confused by the expected output you have listed. Because I don't think your data model supports that output as listed and described.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 04:32:51

select s.sales_id, sp.sales_product_id, sm.sales_message_id, m.mesages_text
from sales s
join sales_product sp
on s.sales_id = sp.sales_id
left join sales_message sm
on s.sales_id = sm.sales_id
left join message_ext m
on sm.message_text_id = m.message_text_id
where s.sales_id in (select sales_id from sales_product group by sales_id having count(*) > 1)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources