Help me with this Query
-
I have 2 tables
a id
x
yb id amount1 amount2 amount3 a_id
1 5 3 5 x
1 2 4 5 yI want result like this -->
x 5 3 5 y 2 4 5how to create query to retrieve this data.can i use pivot .Thank for your help.
In order to help, you need to provide more information about what you are trying to achieve. For example: Can table A have more rows. If so, do you want to still add more columns and keep the data on a single row (I believe that this wouldn't be possible). Can table B have several rows per one row in table A, what happens then etc. Based on the data you provided the following query should provide the result you described, but I think it's hardly what you wanted:
SELECT a1.id, b1.amount1, b1.amount2, b1.amount3,
a2.id, b2.amount1, b2.amount2, b2.amount3
FROM a a1,
b b1,
a a2,
b b2
WHERE a1.id = 'x'
AND b1.a_id = a1.id
AND a2.id = 'y'
AND b2.a_id = a2.idThe need to optimize rises from a bad design. My articles[^]
-
In order to help, you need to provide more information about what you are trying to achieve. For example: Can table A have more rows. If so, do you want to still add more columns and keep the data on a single row (I believe that this wouldn't be possible). Can table B have several rows per one row in table A, what happens then etc. Based on the data you provided the following query should provide the result you described, but I think it's hardly what you wanted:
SELECT a1.id, b1.amount1, b1.amount2, b1.amount3,
a2.id, b2.amount1, b2.amount2, b2.amount3
FROM a a1,
b b1,
a a2,
b b2
WHERE a1.id = 'x'
AND b1.a_id = a1.id
AND a2.id = 'y'
AND b2.a_id = a2.idThe need to optimize rises from a bad design. My articles[^]
table 'a' is dynamic can insert or delete id
than i can not create static query (like the sample that you show me)
the data can be like this
a id
x
y
zb id amount1 amount2 amount3 a_id
1 5 3 5 x
1 2 4 5 y
1 7 8 9 z
2 3 1 3 x
2 4 5 6 y
2 8 3 1 zresult is:
x 5 3 5 y 2 4 5 z 7 8 9 --> id = 1 in table b
x 3 1 3 y 4 5 6 z 8 3 1 --> id = 2 in table b -
table 'a' is dynamic can insert or delete id
than i can not create static query (like the sample that you show me)
the data can be like this
a id
x
y
zb id amount1 amount2 amount3 a_id
1 5 3 5 x
1 2 4 5 y
1 7 8 9 z
2 3 1 3 x
2 4 5 6 y
2 8 3 1 zresult is:
x 5 3 5 y 2 4 5 z 7 8 9 --> id = 1 in table b
x 3 1 3 y 4 5 6 z 8 3 1 --> id = 2 in table bI don't think that what you're asking for is reasonably possible. This would lead to several problems: - how the client side knows which columns are fetched from which row - what if id X in table has 3 rows in table B, but id Y has two rows, what would be the result etc I would look for alternative solutions for the client side. If the client needs the data in several (undetermine amount) columns based on several rows, fetching the data in such format from the database isn't practical. Could you for example use XML formatting for the data from the database and then use XML data at client or should you fetch the correct rows from the database as they are and handle all the formatting at the client. The correct approach depends on the requirements, what is the client side actually doing.
The need to optimize rises from a bad design. My articles[^]