get into big problem-need help

[aligatojohn]

I have two table with one-many relationship table1 has 2 fields:type(int,PK),name table2 has 4 fields:id(PK),type(int,FK reference field type of table1),content,shortDesc How to write a SELECT statement that return rows from table1 join table2(via type field) but each row has a different value of type. Result example: -------------------------------------------------- | table1.type | name | content | ShortDesc | <--- FIELDS NAME -------------------------------------------------- | type1 | name1 | content1| shortDesc1 | <---VALUE( ROW ) -------------------------------------------------- | type2 | name2 content2| shortDesc2| Can anyone help me.Thanks in advance

[Colin Angus Mackay]

SELECT table1.type, table1.name, table2.content, table2.shortDesc
FROM table1
INNER JOIN table2 ON table1.type = table2.type

---

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[aligatojohn]

Sorry but the statement don't return what i expected Example: type1 |Id1 | name1... type1 |Id2 |name2... because there's a one-many relationship between 2 table Thanks anyway

[Colin Angus Mackay]

ali33 wrote:

Sorry but the statement don't return what i expected

However, it does return what you asked for. You asked for: | table1.type | name | content | ShortDesc | The SELECT statement returns: table1.type, table1.name, table2.content, table2.shortDesc name only exists on table1, content and shortDesc only exist on table2 You are now asking for something with an example output of: type1 |Id1 | name1... type1 |Id2 |name2... This is not possible because name is on table1. Therefore any output row that contains type1 must also contain name1. An output row containing type1 cannot contain name2

ali33 wrote:

because there's a one-many relationship between 2 table

Yes, I know that - That is what I gave you. I suspect that either you did not give the full information when you asked the question or you do not understand the implications of the join scenario.

---

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[aligatojohn]

oh!sorry . I have a mistake When I run your SQL statement the result return is: type1 |Id1 | name1... type1 |Id2 |name1... <---(sorry not name2) But the result I expected is: type1| Id1 |name1... type2| id2 |name2... type3| Id3 |name3.... (each type return only one ID of table 2,for all type) Sorry for the mistake and can you help me.Thanks

[Colin Angus Mackay]

ali33 wrote:

each type return only one ID of table 2,for all type

Sorry, but I'm still convinced my SQL is correct. Show me an example of a few rows from each table that should match and tell me for each row in table1 what rows in table2 match to it.

---

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[aligatojohn]

Colin Angus Mackay wrote:

Sorry, but I'm still convinced my SQL is correct. Show me an example of a few rows from each table that should match and tell me for each row in table1 what rows in table2 match to it.

here is an example of some rows from each table: table1: |type |name | ---------------------------- | 1 | type1 | ---------------------------- | 2 | type2 | table2: |id |type | content | shortDesc | --------------------------------------------- |1 | 1 | content1 | desc 1 | |2 | 1 | content2 | desc 2 | |3 | 2 | content3 | desc3 | |4 | 2 | content4 | desc4 | |5 | 1 | content5 | desc5 | The expected result : |id | table1.type | name |content | shortDesc | ------------------------------------------------- | 1 | 1 | type1|content1 | desc1 | | 3 | 2 | type3|content3 |desc3 | each type returns only one row. So now can you help me.If you need,i 'll send you my database and the description more clearly. Thanks.

[Colin Angus Mackay]

I see now. You need to aggregate the rows in table2 somehow then join it. However there is no natural aggregation that you can use. From your example result set you appear to be taking the lowest id for each type - so that is the colum that needs to be aggregated. This is the aggregation component:

SELECT MIN(id) AS id, type
FROM table2
GROUP BY type

It basically takes the first row for each type and discards the rest. Now it has to be joined up. The result of the aggregation is added to the previous join to act as a filter.

SELECT table1.type, table1.name, table2.content, table2.shortDesc
FROM table1
INNER JOIN table2 ON table1.type = table2.type
INNER JOIN (SELECT MIN(id) AS id, type
FROM table2
GROUP BY type) AS filterQuery ON filterQuery.id = table2.id

This should now produce the results that you want.

---

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[aligatojohn]

wonderfull!It run perfectly.Thanks Colin Angus Mackay Best wishes!