August 13, 2014 at 10:07 am
Hi
I have this tables SC and ST
ST has this fields
ref,design,stock
SC has this fields
ref,refb,design
i can join this tables with st.ref=sc.refb
I´ve came with this query
select sc.ref,sc.design,st.stock from st inner join sc on st.ref=sc.refb where sc.refb='xxxx'
it retuns what i need, but field st.stock always assumes the value of the first row
ex:
Ref Design Stock
DT4020.01.337 DISCO OBTURADOR 43,00
DT42S15.03.106 PORCA 43,00
DT42S15.06.497 CORPO 43,00
DT42S15.07.498 SEDE 43,00
and i want this
DT4020.01.337 DISCO OBTURADOR 43,00
DT42S15.03.106 PORCA 0,00
DT42S15.06.497 CORPO 10,00
DT42S15.07.498 SEDE 5,00
Please help
August 13, 2014 at 10:52 am
carlos cachulo-318532 (8/13/2014)
HiI have this tables SC and ST
ST has this fields
ref,design,stock
SC has this fields
ref,refb,design
i can join this tables with st.ref=sc.refb
I´ve came with this query
select sc.ref,sc.design,st.stock from st inner join sc on st.ref=sc.refb where sc.refb='xxxx'
it retuns what i need, but field st.stock always assumes the value of the first row
ex:
Ref Design Stock
DT4020.01.337 DISCO OBTURADOR 43,00
DT42S15.03.106 PORCA 43,00
DT42S15.06.497 CORPO 43,00
DT42S15.07.498 SEDE 43,00
and i want this
DT4020.01.337 DISCO OBTURADOR 43,00
DT42S15.03.106 PORCA 0,00
DT42S15.06.497 CORPO 10,00
DT42S15.07.498 SEDE 5,00
Please help
Add design to your join criteria
select sc.ref, sc.design, st.stock
from st
inner join sc on st.ref=sc.refb AND st.design = sc.design
where sc.refb='xxxx'
August 13, 2014 at 12:05 pm
thanks for the quick reply, unfortenly the query you sent don´t return any result
August 13, 2014 at 12:07 pm
Sample table data would be of help
August 13, 2014 at 12:08 pm
thanks for the quick reply, unfortenly the query you sent don´t return any result
August 13, 2014 at 12:15 pm
carlos cachulo-318532 (8/13/2014)
thanks for the quick reply, unfortenly the query you sent don´t return any result
Did you replace the x's in the WHERE clause with a proper value?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 13, 2014 at 12:19 pm
Table ST
REF c(18)
DESIGN c(60)
STOCK n(10)
Table SC
REF c(18) this fiels exists on both
DESIGN c(60)
REFB c(18)
join: sc.refb=st.ref
The table ST has all the products, the FINISH PRODUCT and the COMPONENTS, whren i say that a REF in ST is a FINISH PRODUCT it inserts into the SC table the several COMPONENTS, these also exist on the ST table
August 13, 2014 at 12:22 pm
yes, didn´t give any error,
August 13, 2014 at 12:24 pm
So what does the data look like (not just the schema) in tables ST and SC?
August 13, 2014 at 12:25 pm
djj (8/13/2014)
Sample table data would be of help
Not much more we can do without the sample data and the query with query results for that sample data.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 13, 2014 at 12:30 pm
Please provide the following:
CREATE TABLE statements for your tables
INSERT INTO statements that populate your tables with relevant data...this data should fully represent the problem you are facing.
details of required results based on the data you have provided.
Thank you.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 13, 2014 at 12:33 pm
ST
REF DESIGN STOCK
A.15.0510 THERMODINAMIC STEAM 10
DT.4020 PORCA DO FILAMENTO 5
DT.4030 CORPO MAQUINADO DFV 45
DT.4040 TAMPA DT425 3
SC
REF REFB DESIGN
DT.4020 A.15.0510 PORCA DO FILAMENTO
DT.4030 A.15.0510 CORPO MAQUINADO DFV
DT.4040 A.15.0510 TAMPA DT425
the query should return when sc.refb=A.15.0510
DT.4020 PORCA DO FILAMENTO 5
DT.4030 CORPO MAQUINADO DFV 45
DT.4040 TAMPA DT425 3
thanks in advance
August 13, 2014 at 12:48 pm
carlos cachulo-318532 (8/13/2014)
STREF DESIGN STOCK
A.15.0510 THERMODINAMIC STEAM 10
DT.4020 PORCA DO FILAMENTO 5
DT.4030 CORPO MAQUINADO DFV 45
DT.4040 TAMPA DT425 3
SC
REF REFB DESIGN
DT.4020 A.15.0510 PORCA DO FILAMENTO
DT.4030 A.15.0510 CORPO MAQUINADO DFV
DT.4040 A.15.0510 TAMPA DT425
the query should return when sc.refb=A.15.0510
DT.4020 PORCA DO FILAMENTO 5
DT.4030 CORPO MAQUINADO DFV 45
DT.4040 TAMPA DT425 3
thanks in advance
Hi carlos....as a sidebar and to enable you to get quicker and tested answers from this forum,,,,
given what you have posted above...how would you create and insert data into tables from what you have posted?
this forum is here to help you...not work for you,
as previously requested
Please provide the following:
CREATE TABLE statements for your tables
INSERT INTO statements that populate your tables with relevant data...this data should fully represent the problem you are facing.
details of required results based on the data you have provided.
Thank you.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 13, 2014 at 1:14 pm
Am I missing something here? There's only 1 record in ST that matches the value in the where clause so you will only get 1 value for Stock.
The query is doing exactly what it should! Your problem is the data, not the query, or you're joining on the wrong column.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 13, 2014 at 1:29 pm
I think I found a way to make it work, but if you ask me, you seem to have a poor database design and a poor understanding of how the tables relate to each other.
This seems to work for the data you supplied.
SELECT sc.ref,sc.design,st2.stock
FROM ST
INNER JOIN SC
on st.ref=sc.refb
INNER JOIN ST ST2
ON SC.Design = ST2.Design
WHERE SC.refb='A.15.0510'
Or even simpler:
SELECT sc.ref,sc.refb,sc.design,st.stock
FROM ST
INNER JOIN SC
on st.Design=sc.Design
WHERE SC.refb='A.15.0510'
I came up with the above queries by assuming how the tables were related. Even though these work with the data supplied, I cannot guarantee this are correct and they'll work with other data.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply