mysql helps

[hsteo]

I intending to join to 2 tables based on 2 conditions:

Assume that
table 1 has rows: Able, Bravo, Cam
table 2 has rows: Able, Donkey, Eagle

Mysql code is:

SELECT table1.bravo, table1.cam left join table2 on table1.able = table2.able where donkey = 1

But i have another condition and that is able must be of certain value. how do i code for that?

thanx

[HairyMonkeyMan]

Hi hsteo...

By the look of your query, you are taking all rows from table 1.. joining table 2 where the able column matches (left joined). Then you have a where condition on table2.donkey = 1.

I would put these inside an inner join.. i.e:
SELECT table1.bravo, table1.cam
FROM table1
INNER JOIN table2 ON table1.able = table2.able
AND table2.donkey = 1
AND able = ?

ps: this should be in the database forum,

best of luck

[PeejAvery]

[ moved ]

[hsteo]

Hi hsteo...

By the look of your query, you are taking all rows from table 1.. joining table 2 where the able column matches (left joined). Then you have a where condition on table2.donkey = 1.

I would put these inside an inner join.. i.e:
SELECT table1.bravo, table1.cam
FROM table1
INNER JOIN table2 ON table1.able = table2.able
AND table2.donkey = 1
AND able = ?

ps: this should be in the database forum,

best of luck

should it be a 'WHERE'??

[hsteo]

hi,

currently i am working on a database using vb .net and met with an error

i have 2 tables:
table1: able, boy, cat
table2: able, donkey, eagle

now i wan to extract eagle from table2 using table1.able

so my code is as follows

SELECT table2.eagle
FROM table2
INNER JOIN table1 ON table1.able = table2.able
WHERE table2.donkey = 2
AND table1.able = 2

and my latter code is here

Dim da As SqlDataAdapter = New SqlDataAdapter(sql, sqlConn)
da.Fill(ds, "getCleaningChargeList") ' getCleaningChargeList is a virtual table

However the code always throw me an error at fill statement. Any idea why?

Thanx

[PeejAvery]

Yes. I am sure it was just a typo.

SELECT table1.bravo, table1.cam FROM table1 INNER JOIN table2 ON table1.able = table2.able WHERE table2.donkey = 1AND able = ?

[dglienna]

Post only one time, but search before you do

[HairyMonkeyMan]

You can do it in the where as PeejAvery described or in multiple join conditions. Results of the query should be similar... if your interested in performance, use the 'EXPLAIN' statement to guage the speed of execution (try each approach).

[HairyMonkeyMan]

Lets see the select code from your data adapter. What is the exact error message?

[hsteo]

Hi thanx for the superby helpful hints and reply.

In order not to spam the forum, here is another problem:

As usual take 2 tables for example:

table1:
able 21/01/03
bravo 01/10/07
cat 03/09/07
able 09/05.05
bravo 07/06/06

ok so now i would like to have the following result:

able 09/05/05
bravo 01/10/07
cat 03/09/07

Seriously i thought of using TOP in my query but couldn't display the rest of the list out. Any suggestion?

Thanx

[HairyMonkeyMan]

top doesn't work in mysql.... its only for msaccess (as far as i know).

You can use 'LIMIT'. To limit to the first 3 rows, your query might look like this:

SELECT table1.bravo, table1.cam
FROM table1
INNER JOIN table2
ON table1.able = table2.able
AND table2.donkey = 1
AND able = ?
LIMIT 3

[HairyMonkeyMan]

I might have that back-to-front a little, since you have your data in fields..

Have a play around with 'LIMIT' and let me know how you get on ;)

[HanneSThEGreaT]

Are these :
table1: able, boy, cat
table2: able, donkey, eagle

Really your field names ¿
Not good design, if it is.

[PeejAvery]

[ merged ]

[hsteo]

top doesn't work in mysql.... its only for msaccess (as far as i know).

You can use 'LIMIT'. To limit to the first 3 rows, your query might look like this:

SELECT table1.bravo, table1.cam
FROM table1
INNER JOIN table2
ON table1.able = table2.able
AND table2.donkey = 1
AND able = ?
LIMIT 3


oops guess i was misleading in the first place, anyway now I am trying to use just 1 table with the following data values

able 21/03/08
bravo 12/02/05
charlie 13/09/06
delta 23/04/06
able 15/10/05
delta 10/10/05

if for example i want to look up for data that occurs between 1/11/05 - 1/05/06

then the result must be as follows:

able 15/10/05
delta 23/04/06

HTH thanx

[PeejAvery]

hsteo, This is all pretty much one problem. Please keep it contained here rather than posting across multiple forums. This only creates confusion. Users will have to surf the forums just to see all the replies.

Thanks. :wave:

[HairyMonkeyMan]

Is this design of table needed in your application?

It would be far easier to query if you had something like:


NAME | VALUE
------------------------
able | 12/1/2005
donkey | 1/5/2006

[hsteo]

Is this design of table needed in your application?

It would be far easier to query if you had something like:


NAME | VALUE
------------------------
able | 12/1/2005
donkey | 1/5/2006


The idea for that design is to locate those items that took place within the time specified however if there is no occurance off such event, then the date should be chosen such that it is smaller than the range of the date.

I am also helpless at those demands

[HairyMonkeyMan]

If you were to use my idea for a design, you could query for things between dates like this:

select * from table
where `name` = 'able'
and date(value) between date(?date1) and date(?date2)