Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: Help required for writing query

    Hello friends,
    can u help me writing a simple query.
    I have 2 tables user, mac_details
    Structure of both tables is given below.

    >
    >user
    >-----
    >user_id smallint primary_key
    >user_name varchar(30)
    >current_mac_id smallint Foreign_key
    >
    >mac_details
    >------------
    >mac_id smallint primary_key
    >mac_name varchar(20)


    Every user is assigned one mac_id.
    I want to select those mac_id & mac_name from mac_details which r not yet assigned to any user.
    Thats it.
    Constraint I have is that I can not use sub query.
    B'coz MySQL don't support subquery.
    Following is sample data.

    +---------+-----------+----------------+
    | user_id | user_name | current_mac_id |
    +---------+-----------+----------------+
    | 1 | Sachin | 8 |
    | 2 | Sourav | 9 |
    | 3 | Rahul | 5 |
    +---------+-----------+----------------+

    +--------+-------------+
    | mac_id | mac_name |
    +--------+-------------+
    | 1 | Wipro |
    | 2 | IBM_Desktop |
    | 3 | Zenith_PC |
    | 4 | Compaq |
    | 5 | HP |
    | 6 | HP_Laptop |
    | 7 | Unknown |
    | 8 | Whatever |
    | 9 | Machine |
    | 10 | MMM |
    +--------+-------------+




    Regards
    Amit

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You probably should discuss this with your instructor, but I can give you a hint: use a left join, then check for NULL values.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    16

    Question

    I am not getting it.
    Can anybody help writing a query.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You bet I'll help. What questions do you have?

    -PatP

  5. #5
    Join Date
    Jul 2003
    Posts
    73
    Quote Originally Posted by amit_28oct
    I am not getting it.
    Can anybody help writing a query.
    Code:
    SELECT mac_id, mac_name
    FROM mac_details
      LEFT OUTER JOIN user ON mac_details.mac_id = user.current_mac_id
    WHERE user.user_id IS NULL
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  6. #6
    Join Date
    Mar 2004
    Posts
    16

    Thanks very much joeldixon66.

    Thanks very much joeldixon66. That query is working perfectly fine.
    I required exactly that.
    Thanks once again.
    Regards
    Amit

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I just hate it when somebody whines until they get another person to do their homework for them. It annoys me a bunch more when I might hire one of them, since that means that I'll have to sort out the mess later!

    -PatP

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    PatP

    Well I'm not in the position to hire anyone but I agree with you on that. I answer on this and a few other message boards. Generally I think a nudge in the right direction (especially on something this simple) is better than offering an outright solution. Gets people thinking of how to do the next problem sooner.

    Two months ago I wasn't as efficient with joins, case, union as I am now. Part of it is folks like yourself, Rudy and others who offer solutions but also trial and error on my part.

  9. #9
    Join Date
    Jul 2003
    Posts
    73
    Quote Originally Posted by PatP
    I just hate it when somebody whines until they get another person to do their homework for them. It annoys me a bunch more when I might hire one of them, since that means that I'll have to sort out the mess later!
    Sorry if I answered it too soon Pat. I did see you try to give Amit something to think about the solution - that's why I waited a while before posting. When he replied that he still had no idea - I guess I couldn't help myself!

    I've just spent my last two weeks bogged down in Outer Joins and Inner Queries etc for a Work Request I'm doing - and I swear when I got to bed at night I see the SQL code jogging over my eyelids!
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  10. #10
    Join Date
    Mar 2004
    Posts
    480
    I don't think Pat was faulting you Joel, only it would be nice to see someone say, hmmm I looked at what you suggested, here is what I came up with and it doesn't work, rather than wait for an answer.

  11. #11
    Join Date
    Jul 2003
    Posts
    73
    Quote Originally Posted by guelphdad
    I don't think Pat was faulting you Joel, only it would be nice to see someone say, hmmm I looked at what you suggested, here is what I came up with and it doesn't work, rather than wait for an answer.
    Yeah - fair enough I guess.

    I must have been schmoozed by the "Hello friends," in Amit's initial question.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  12. #12
    Join Date
    Mar 2004
    Posts
    16
    I am just shocked to listen such type of remarks.
    Let me tell u pepole that I tried every thing I can to solve this.
    As I am from oracle background. I tried using subqueries & minus operator. But that did'nt work for mysql. This is true that I am week with joins. But I am working on it.

    After reciveing that solution I read about Left outer joins & other joins. Previously I know abt only Inner,outer,left & full joins.
    I was not knowing that we can use 2 types of join in single query.
    Now atleast I know it. & I can work on it.
    So I just want to say PatP that if u can't or u don't help don't discourage others to do so.
    Forums are ment for sharing knowledge & helping each other not for asking riddels & giving assurance that 'You bet I'll help. What questions do you have?'
    I once again thank to joeldixon66 b'coz due to that query I came to know lots of things & solved many other problems as well.

    Amit

  13. #13
    Join Date
    Mar 2004
    Posts
    480
    Amit

    On the other side of the coin from yourself are many people that drop by these forums that have no idea what they are doing, pose homework questions, get the answers and flee, never to offer help in the future. They haven't actually learned anything, yet will probably pass their course as a result. Pat is right calling out this kind of stuff particularly when he is a person, who may down the road, have to hire one of these people.

    Two things might have helped in your case: a) outline that you are familiar with oracle b) offer what steps/code you have tried and didn't work so people on a forum can see that you have made an attempt at finding a solution on your own.

  14. #14
    Join Date
    Mar 2004
    Posts
    16
    ok.
    That may be the case that some pepole miss use forums.
    But Every one is not like that.
    Before making such type of remarks anyone should think.

    I think that suggestions of guelphdad r right.
    I will remember them next time.
    But if anyone have told that suggestions earlier then their would be no such discussions at all.

    Anyway it is not too late.

    Regards
    Amit

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe I'm too jaded, but I've been taken advantage of too many times.

    About a year ago we were interviewing candidates for some developer positions. Our Director of Development discovered some code in one of the school work samples presented that looked suspiciously like mine. He came and asked me about it during the interview, and fortunately I was able to retrieve the initial message thread... Needless to say, that candidate was doomed!

    You presented an example that still looks for all the world like a homework assignment to me. You stated the question well and gave a good example, but didn't even mention that you'd attempted to solve the problem yourself.

    I offered what I thought was a good hint, more than enough to get you on the right track, but you'd still have to think the problem through on your own.

    When you responded that you didn't understand, you still didn't offer any explanation of what you'd tried or anything to give me a clue that you'd already tried to solve the problem at all. Please bear in mind that I dislike being taken advantage of when you think about my reply...

    I certainly didn't mean to antagonize you, but what I could see looked like a kid trying to get me to do their homework for them, that was pouting because I didn't deliver the completed assignment to their satisfaction. I'm sorry if I misinterpreted what you wrote, but even now it still looks that way to me.

    As for Joel, I know the feeling. Sometimes I help too much myself. Right now I'm "up to my eyeballs" in stuff at work, but I still make time to check in on a couple of the SQL forums, just because I've gotten help from them in the past and know how much that can mean when your back is against the wall and a deadline is headed your way. The only thing I'd ask is that you at least draw the poster out to see what they've tried instead of offering up blanket solutions... If we (a web site) gets a reputation as being an "easy source of homework answers", the overall quality of the site deteriorates rapidly!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •